with result_table as (
    select t1.waybill_id                       as waybill_id               --运单号
         , t1.project_first_type_code          as project_first_type_code  --项目一级
         , t1.project_first_type_name          as project_first_type_name  --项目一级
         , t1.project_second_type_name         as project_second_type_name --项目二级
         , t1.customer_code                    as customer_code            --客户编码
         , t1.customer_name                    as customer_name            --客户名称
         , t1.dt                               as date_time                --日期
         , if(t3.waybill_no is not null, 1, 0) as complaint_flag           --1代表客诉标识
         , if(t3.sham_flag = 1, 1, 0)          as sham_flag                --1代表虚假标识
         , if(t3.delay_flag = 1, 1, 0)         as delay_flag               --1代表延误标识
         , if(t4.waybill_no is not null, 1, 0) as worn_flag                --1代表破损标识
         , t1.dt
    from (
             select t1.waybill_id
                  , t1.customer_code                                --客户编码
                  , t1.customer_name
                  , t1.dt                                           --分区
                  , t2.first_type_name  as project_first_type_code  --项目一级
                  , t2.first_type_code  as project_first_type_name  --项目一级
                  , t2.second_type_name as project_second_type_name --项目二级
             from jms_dwd.dwd_wide_customer_order_waybill_dt t1
                      join jms_dim.dim_sqs_project_customer_config_base t2
                           on t1.customer_code = t2.customer_code
             where t1.dt <= '{{ execution_date | cst_ds }}'
               and t1.dt >= date_add('{{ execution_date | cst_ds }}', -6)
               and t1.order_status_code = '103'
         ) t1
             left join
         (--客诉表(虚假签收,延误)
             select waybill_no
                  , max(sham_flag)  as sham_flag
                  , max(delay_flag) as delay_flag
             from (
                      select waybill_no
                           , if((work_type = '普通工单' and second_type_code = '2104') or (work_type = '平台工单'), 1,
                                0) as sham_flag
                           , if((work_type = '普通工单' and second_type_code = '2101'), 1,
                                0) as delay_flag
                      from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                      where dt <= '{{ execution_date | cst_ds }}'
                        and dt >= date_add('{{ execution_date | cst_ds }}', -6)
                        and is_delete = 2
                  ) t
             group by waybill_no
         ) t3
         on t1.waybill_id = t3.waybill_no
             left join
         (--问题件表(破损)
             select waybill_no
             from jms_dwd.dwd_sqs_problem_express_wo_base_dt
             where dt <= '{{ execution_date | cst_ds }}'
               and dt >= date_add('{{ execution_date | cst_ds }}', -6)
               and problem_type_subject_code in ('27a', '27b', '27c', '27h', '35a') --破损
             group by waybill_no
         ) t4
         on t1.waybill_id = t4.waybill_no
--     where t1.rn = 1
)
insert
overwrite
table
jms_dm.dm_sqs_project_customer_waybill_detail_dt
partition
(
dt
)
select waybill_id
     , project_first_type_code
     , project_first_type_name
     , project_second_type_name
     , customer_code
     , customer_name
     , date_time
     , complaint_flag
     , sham_flag
     , delay_flag
     , worn_flag
     , dt
from result_table
    distribute by dt
;